package com.mr3chi.horoscopes;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBAdapter {
	
	private static final int 	DATABASE_VERSION 	= 2;
	private static final String DATABASE_NAME 		= "HoroscopeDB";
	
    private static final String HOROSCOPE_TABLE 	= "horoscope";
    private static final String HOROSCOPE_ID 		= "_id";
    private static final String HOROSCOPE_HOROSCOPE = "horo";
    private static final String HOROSCOPE_DATE 	= "date";
    private static final String HOROSCOPE_CONTENT 	= "content";
    
    private static final String TODAYNEWS_TABLE 	= "news";
    private static final String TODAYNEWS_ID 		= "_id";
    private static final String TODAYNEWS_TITLE 	= "title";
    private static final String TODAYNEWS_IMAGE		= "image";
    private static final String TODAYNEWS_CONTENT 	= "content";
    private static final String TODAYNEWS_LINK		= "link";
    private static final String TODAYNEWS_PAGEID	= "time";
    private static final String TODAYNEWS_DATE		= "date";
    
    private static final String CREATE_TABLE_HOROSCOPE = "CREATE TABLE "+ HOROSCOPE_TABLE  + 
    		"(" +
    		HOROSCOPE_ID 		+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +
    		HOROSCOPE_HOROSCOPE	+ " TEXT, " +
    		HOROSCOPE_DATE		+ " TEXT, " +
    		HOROSCOPE_CONTENT 	+ " TEXT " +
            ");";

    private static final String CREATE_TABLE_NEWS = "CREATE TABLE "+ TODAYNEWS_TABLE  + 
    		"(" +
    		TODAYNEWS_ID 		+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			TODAYNEWS_TITLE 	+ " TEXT, " +
			TODAYNEWS_IMAGE 	+ " TEXT, " +
			TODAYNEWS_CONTENT 	+ " TEXT, " +
			TODAYNEWS_LINK 		+ " TEXT, " +
			TODAYNEWS_PAGEID 	+ " TEXT, " +
			TODAYNEWS_DATE		+ " TEXT " +
			");";
    
    
    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;
    
    public DBAdapter(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
    
    public DBAdapter open() throws SQLException{
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close(){
    	DBHelper.close();
    }

    
    ///////////////////////////////////////////////
    //	NEWS FUNC HERE
    ///////////////////////////////////////////////
    
    public long insertNews(News news){
        ContentValues initialValues = new ContentValues();
        
        initialValues.put(TODAYNEWS_TITLE, 		news.getTitle());
        initialValues.put(TODAYNEWS_IMAGE, 		news.getImage());
        initialValues.put(TODAYNEWS_CONTENT, 	news.getDesc());
        initialValues.put(TODAYNEWS_LINK, 		news.getLink());
        initialValues.put(TODAYNEWS_PAGEID, 	news.getPageId());
        initialValues.put(TODAYNEWS_DATE, 		news.getDateString());
        
        return db.insert(TODAYNEWS_TABLE, null, initialValues);
    }
    
    public List<News> getAllNews(){
        Cursor cursor = db.query(TODAYNEWS_TABLE, new String[]{
        		TODAYNEWS_ID, TODAYNEWS_TITLE, TODAYNEWS_IMAGE, 
        		TODAYNEWS_CONTENT, TODAYNEWS_LINK, TODAYNEWS_PAGEID, TODAYNEWS_DATE},
                null, null, null, null, null, null);
        
        List<News> list = new ArrayList<News>();
    	if(cursor.moveToFirst()){
			do{
				News temp = new News();
				temp.setId(cursor.getInt(0));
				temp.setTitle(cursor.getString(1));
				temp.setImage(cursor.getString(2));
				temp.setDesc(cursor.getString(3));
				temp.setLink(cursor.getString(4));
				temp.setPageId(cursor.getString(5));
				temp.setDateString(cursor.getString(6));
				list.add(temp);
			}while(cursor.moveToNext());
		}
    	return list;
    }
    
    public List<News> getNewsByPageId(String pageId){
    	Cursor cursor = db.query(TODAYNEWS_TABLE, new String[]{
        		TODAYNEWS_ID, TODAYNEWS_TITLE, TODAYNEWS_IMAGE, 
        		TODAYNEWS_CONTENT, TODAYNEWS_LINK, TODAYNEWS_PAGEID, TODAYNEWS_DATE},
        		TODAYNEWS_PAGEID + "=?", new String[] {pageId}, null, null, null, null);
        
        List<News> list = new ArrayList<News>();
    	if(cursor.moveToFirst()){
			do{
				News temp = new News();
				temp.setId(cursor.getInt(0));
				temp.setTitle(cursor.getString(1));
				temp.setImage(cursor.getString(2));
				temp.setDesc(cursor.getString(3));
				temp.setLink(cursor.getString(4));
				temp.setPageId(cursor.getString(5));
				temp.setDateString(cursor.getString(6));
				list.add(temp);
			}while(cursor.moveToNext());
		}
    	return list;
    }
 
    public News getNewsByNewsId(int newsId){
    	String strID = String.valueOf(newsId);
        Cursor cursor = db.query(TODAYNEWS_TABLE, new String[]{
        		TODAYNEWS_ID, TODAYNEWS_TITLE, TODAYNEWS_IMAGE, 
        		TODAYNEWS_CONTENT, TODAYNEWS_LINK, TODAYNEWS_PAGEID, TODAYNEWS_DATE},
        		TODAYNEWS_ID + "=?", new String[]{strID}, null, null, null, null);
        
        if(cursor.moveToFirst()){
        	News temp = new News();
        	temp.setId(cursor.getInt(0));
			temp.setTitle(cursor.getString(1));
			temp.setImage(cursor.getString(2));
			temp.setDesc(cursor.getString(3));
			temp.setLink(cursor.getString(4));
			temp.setPageId(cursor.getString(5));
			temp.setDateString(cursor.getString(6));
			return temp;
        }
        return null;
    }
    
    public int checkNewsByLink(String link){
    	Cursor cursor = db.query(TODAYNEWS_TABLE, new String[]{
    			TODAYNEWS_ID, TODAYNEWS_TITLE, TODAYNEWS_IMAGE, 
        		TODAYNEWS_CONTENT, TODAYNEWS_LINK, TODAYNEWS_PAGEID, TODAYNEWS_DATE},
        		TODAYNEWS_LINK + "=?", new String[]{link}, 
        		null, null, null, null);
    	if(cursor.moveToFirst()){
    		return cursor.getInt(0);
    	}
    	return -1;
    }
 
    public boolean updateNewsById(News param){
        ContentValues args = new ContentValues();

        args.put(TODAYNEWS_TITLE, param.getTitle());
        args.put(TODAYNEWS_IMAGE, param.getImage());
        args.put(TODAYNEWS_CONTENT, param.getDesc());
        args.put(TODAYNEWS_LINK, param.getLink());
        args.put(TODAYNEWS_PAGEID, param.getPageId());
        args.put(TODAYNEWS_DATE, param.getDateString());
        
        String strID = String.valueOf(param.getId());
        return db.update(TODAYNEWS_TABLE, args, TODAYNEWS_ID + "=?" , new String[]{strID}) > 0;
    }
    
    public boolean deleteNewsById(int newsId){
    	String strID = String.valueOf(newsId);
        return db.delete(TODAYNEWS_TABLE, TODAYNEWS_ID + "=?", new String[]{strID}) > 0;
    }

	///////////////////////////////////////////////
	//	HORO FUNC HERE
	///////////////////////////////////////////////
    
//  HOROSCOPE_HOROSCOPE
//	HOROSCOPE_DATETYPE	
//	HOROSCOPE_REALDATE
//	HOROSCOPE_CONTENT
    public long insertHoro(NewsHoroscope item){
        ContentValues initialValues = new ContentValues();
        
        initialValues.put(HOROSCOPE_HOROSCOPE, item.getHoroscopeName());
        initialValues.put(HOROSCOPE_DATE, item.getDateString());
        initialValues.put(HOROSCOPE_CONTENT, item.getContent());

        return db.insert(HOROSCOPE_TABLE, null, initialValues);
    }
    
    public List<NewsHoroscope> getAllHoros(){
    	Cursor cursor = db.query(HOROSCOPE_TABLE, new String[]{
        		HOROSCOPE_ID, HOROSCOPE_HOROSCOPE, 
        		HOROSCOPE_DATE, HOROSCOPE_CONTENT},
                null, null, null, null, null, null);
        
        List<NewsHoroscope> list = new ArrayList<NewsHoroscope>();
    	if(cursor.moveToFirst()){
			do{
				NewsHoroscope temp = new NewsHoroscope();
				temp.setId(cursor.getInt(0));
				temp.setHoroscopeName(cursor.getString(1));
				temp.setDateString(cursor.getString(2));
				temp.setContent(cursor.getString(3));
				list.add(temp);
			}while(cursor.moveToNext());
		}
    	return list;
    }
    
    public List<NewsHoroscope> getAllHorosByHoroName(String name){
    	Cursor cursor = db.query(HOROSCOPE_TABLE, new String[]{
        		HOROSCOPE_ID, HOROSCOPE_HOROSCOPE, 
        		HOROSCOPE_DATE, HOROSCOPE_CONTENT},
        		HOROSCOPE_HOROSCOPE + "=?", new String[]{name}, null, null, null, null);
        
        List<NewsHoroscope> list = new ArrayList<NewsHoroscope>();
    	if(cursor.moveToFirst()){
			do{
				NewsHoroscope temp = new NewsHoroscope();
				temp.setId(cursor.getInt(0));
				temp.setHoroscopeName(cursor.getString(1));
				temp.setDateString(cursor.getString(2));
				temp.setContent(cursor.getString(3));
				list.add(temp);
			}while(cursor.moveToNext());
		}
    	return list;
    }
    
    public List<NewsHoroscope> getAllHorosByHoroNameAndDate(String name, String date){
    	Cursor cursor = db.query(HOROSCOPE_TABLE, new String[]{
        		HOROSCOPE_ID, HOROSCOPE_HOROSCOPE, 
        		HOROSCOPE_DATE, HOROSCOPE_CONTENT},
        		HOROSCOPE_HOROSCOPE + "=? AND " + HOROSCOPE_DATE + "=?", new String[]{name, date}, null, null, null, null);
        
        List<NewsHoroscope> list = new ArrayList<NewsHoroscope>();
    	if(cursor.moveToFirst()){
			do{
				NewsHoroscope temp = new NewsHoroscope();
				temp.setId(cursor.getInt(0));
				temp.setHoroscopeName(cursor.getString(1));
				temp.setDateString(cursor.getString(2));
				temp.setContent(cursor.getString(3));
				list.add(temp);
			}while(cursor.moveToNext());
		}else{
			return null;
		}
    	return list;
    }
    
    public boolean deleteHoroById(int horoID)
    {
    	String strID = String.valueOf(horoID);
    	return db.delete(HOROSCOPE_TABLE, HOROSCOPE_ID + "=?", new String[]{strID}) > 0;
    }
 
    public boolean updateHoroById(NewsHoroscope item){
        ContentValues args = new ContentValues();
        
        args.put(HOROSCOPE_CONTENT, item.getContent());
        args.put(HOROSCOPE_DATE, item.getDateString());
        args.put(HOROSCOPE_HOROSCOPE, item.getHoroscopeName());
        
        String strID = String.valueOf(item.getId());
        return db.update(HOROSCOPE_TABLE, args, HOROSCOPE_ID + "=?", new String[]{strID}) > 0;
    }
    
    
	///////////////////////////////////////////////
	//	DBHelper HERE
	///////////////////////////////////////////////
    private static class DatabaseHelper extends SQLiteOpenHelper{
 
        public DatabaseHelper(Context context) {
        	super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
 
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_NEWS);
            db.execSQL(CREATE_TABLE_HOROSCOPE);
        }
 
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
            db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_NEWS);
            db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_HOROSCOPE);
            onCreate(db);
        }   
    }    
}
